package com.dragon.repository.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.dragon.repository.entity.Stock;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 库存表 Mapper 接口
 * </p>
 *
 * @author sy
 * @since 2023-11-13
 */
@Mapper
public interface StockMapper extends BaseMapper<Stock> {

    @Select("<script>" +
            "SELECT " +
            "M.*  " +
            "FROM " +
            "(SELECT " +
            "a.*, " +
            "b.name AS warehouseName, " +
            "cc.spu_specification AS specificationName, " +
            "d.name AS unitName, " +
            "cc.name AS productName, " +
            "ccc.name AS categoryName, " +
            "cc.category_id AS categoryId "+
            "FROM " +
            "dragon_stock a " +
            "LEFT JOIN dragon_warehouse b ON a.warehouse_id = b.id " +
            //"LEFT JOIN dragon_product_specs c ON a.product_sku_id = c.id " +
            "LEFT JOIN dragon_product_unit d ON d.id = a.unit_id " +
            "LEFT JOIN dragon_product cc ON a.product_sku_id = cc.id " +
            "LEFT JOIN dragon_product_category ccc ON cc.category_id = ccc.id "+
            ") M WHERE 1=1 " +
            "<if test='search != null and search != \"\"'>"+
            " AND M.warehouseName like CONCAT('%',#{search},'%') or M.productName like CONCAT('%',#{search},'%') " +
            "</if>"+
            "<if test='warehouseId != null and warehouseId != \"\"'>"+
            " AND M.warehouse_id = #{warehouseId} " +
            "</if>"+
            "<if test='categoryId != null and categoryId != \"\"'>"+
            " AND M.categoryId = #{categoryId} " +
            "</if>"+
            "</script>")
    Page<Stock> selectByName(Page<Stock> stockPage, String search, Integer warehouseId, Integer categoryId);


    @Select("<script>" +
            "SELECT " +
            "M.*  " +
            "FROM " +
            "(SELECT " +
            "a.*, " +
            "b.name AS warehouseName, " +
            "cc.spu_specification AS specificationName, " +
            "d.name AS unitName, " +
            "cc.name AS productName, " +
            "ccc.name AS categoryName, " +
            "cc.category_id AS categoryId "+
            "FROM " +
            "dragon_stock a " +
            "LEFT JOIN dragon_warehouse b ON a.warehouse_id = b.id " +
            //"LEFT JOIN dragon_product_specs c ON a.product_sku_id = c.id " +
            "LEFT JOIN dragon_product_unit d ON d.id = a.unit_id " +
            "LEFT JOIN dragon_product cc ON a.product_sku_id = cc.id " +
            "LEFT JOIN dragon_product_category ccc ON cc.category_id = ccc.id "+
            ") M WHERE 1=1 " +
            "<if test='search != null and search != \"\"'>"+
            " AND M.warehouseName like CONCAT('%',#{search},'%') or M.productName like CONCAT('%',#{search},'%') " +
            "</if>"+
            "<if test='warehouseId != null and warehouseId != \"\"'>"+
            " AND M.warehouse_id = #{warehouseId} " +
            "</if>"+
            "<if test='categoryIds.size != 0 and categoryIds.size != null '>" +
            " AND M.categoryId in " +
            "<foreach collection = 'categoryIds' item = 'item' open = '(' close = ')' separator = ','>" +
            "#{item}" +
            "</foreach>" +
            "</if>"+
            "</script>")
    Page<Stock> selectByNameTwo(Page<Stock> stockPage, String search, Integer warehouseId, List<Integer> categoryIds);

    //根据仓库id和物品id 查库存
    @Select("select a.* from dragon_stock a " +
            "LEFT JOIN  (SELECT a.*,aa.claims_product_id from dragon_warehouse_inout a LEFT JOIN dragon_inout_product aa on a.id = aa.inout_id) M" +
            " on M.claims_product_id = a.product_sku_id AND M.warehouse_id = a.warehouse_id " +
            "WHERE M.claims_product_id = #{productId} AND M.warehouse_id = #{warehouseId}")
    Stock selectOneByWarehouseAndProuctId(Integer warehouseId, Integer productId);

    @Select("SELECT a.*,aaa.name as productName, aa.specification_name as specificationName, b.name as unitName FROM `dragon_stock` a " +
            "LEFT JOIN dragon_product aaa on a.product_sku_id = aaa.id " +
            "LEFT JOIN dragon_product_unit b on a.unit_id = b.id WHERE a.warehouse_id = #{warehouseId} ")
    List<Stock> selectByWarehouseId(Integer warehouseId);

}
